package cn.telling.product.dao.Impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import cn.telling.common.util.TellingConstance;
import cn.telling.product.dao.IDirectionalDao;
import cn.telling.product.vo.AreaInfoVo;
import cn.telling.product.vo.BuyerVo;
import cn.telling.product.vo.StoreProductVo;
import cn.telling.product.vo.SupplyArea0Vo;
import cn.telling.utils.AutoInjection;
import cn.telling.utils.BaseUtil;
import cn.telling.utils.StringHelperTools;

/**
 * 
 * @ClassName: DirectionalDaoImpl 
 * @Description: 产品定向发布数据服务接口的实现
 * @author 陈赟 
 * @date 2015年6月16日 下午7:25:48 
 *
 */
@Repository("directionalDao")
public class DirectionalDaoImpl implements IDirectionalDao {
    
	private static Logger LOGGER = LoggerFactory.getLogger(DirectionalDaoImpl.class);
	
	@Autowired
	@Qualifier("film-template")
	private JdbcTemplate jdbcTemplate;
	
	@Override
    public List<Map<String, String>> getProductList(String supplyId, String sortId1, String sortId2, String productName,String parentBrand,String childBrand) {
        StringBuilder sb = new StringBuilder();
        sb.append("select p.productId as productId, p.productname as productName,");
        sb.append(" pb.brandName as bname, p.productCode as pCode, p.color as color,");
        sb.append(" sp.supplyId as supplyId,sp.id as shopProductId");
        sb.append(" from product_main p,product_brand pb, shop_product sp");
        sb.append(" where p.brandid = pb.brandId and p.del_flag = ?");
        sb.append(" and sp.productid = p.productid and sp.salemode = ?");
        sb.append(" and sp.isvalid = ?");
        sb.append(" and sp.del_flag = ?");
        sb.append(" and p.saleType = '2'"); // 只取销售模式为：通讯市场的产品
        sb.append(" and sp.supplyid = ?");
        sb.append(" and pb.del_flag = ?");
        List<Object> args = new ArrayList<Object>();
        args.add(TellingConstance.DEL_FLAG_1);
        args.add(TellingConstance.APPOINTMODE);
        args.add(TellingConstance.IS_VALID_01);
        args.add(TellingConstance.DEL_FLAG_1);
        args.add(supplyId);
        args.add(TellingConstance.DEL_FLAG_1);
        if(!"".equals(sortId2)){
			if("yes".equals(sortId1)){
				sb.append(" and p.SORTID = ?");
	            args.add(sortId2);
			}else if("no".equals(sortId1)){ 
				sb.append(" and p.SORTID in (select SORTID from product_sort where DEL_FLAG=? and ISVALID=? start with PARENTID=? connect by prior  SORTID=PARENTID)");
	            args.add(TellingConstance.DEL_FLAG_1);
	            args.add(TellingConstance.IS_VALID_01);
	            args.add(sortId2);
			}
		}else{
			 sb.append(" and 1=1");
		}
        if(!"".equals(productName)) {
            sb.append(" and lower(replace(p.productname,' ','') ) like ?");
            args.add("%"+productName.replaceAll("\\s*", "").toLowerCase()+"%");
        }
        if(!"".equals(childBrand)){
        	if("yes".equals(parentBrand)){
				sb.append(" and pb.brandid = ?");
	            args.add(childBrand);
			}else if("no".equals(parentBrand)){ 
				sb.append("and pb.brandid in (select pb.brandid from product_brand pb where pb.del_flag=? and pb.isvalid=? start with pb.parentid=?  connect by prior pb.brandid=pb.parentid)");
	            args.add(TellingConstance.DEL_FLAG_1);
	            args.add(TellingConstance.IS_VALID_01);
	            args.add(childBrand);
			}
        }
        sb.append(" order by p.productname");
        
        String sql = sb.toString();
        LOGGER.debug("获取所有有效的产品列表:" + BaseUtil.logSQL(sql, args.toArray()));
        List<Map<String, String>> list = jdbcTemplate.query(sql, args.toArray(), new ValueMapperSelf2());
        return list;
    }
	/**
	 * 
	 * @ClassName: ValueMapperSelf2 
	 * @Description: 数据转换
	 * @author 陈赟 
	 * @date 2015年6月16日 下午3:51:30 
	 *
	 */
    private static final class ValueMapperSelf2 implements RowMapper<Map<String, String>> {
        public Map<String, String> mapRow(ResultSet rs, int rowNum) throws SQLException {
            Map<String, String> map = new HashMap<String, String>();
            map.put("productId",StringHelperTools.nvl(rs.getString("productId")));
            map.put("productName", StringHelperTools.nvl(rs.getString("productName")));
            map.put("supplyId", StringHelperTools.nvl(rs.getString("supplyId")));
            map.put("shopProductId",StringHelperTools.nvl(rs.getString("shopProductId")));
            map.put("checked", "0");// 默认未选中
            return map;
        }
    }
	
	@Override
    public List<AreaInfoVo> getAreaLsByAreaId(String areaId, String level) {
	    String sql = "";
        if(level.equals("1") || level.equals("2")){
            sql = "select * from areainfo ai where status='1' and ai.parentid = ? ";
        }else{
            sql = "select * from areainfo ai where status='1' and ai.areaid = ? ";
        }
        Object[] args = new Object[]{areaId};
        args = StringHelperTools.nullToString(args);
        
        LOGGER.debug("查询区域表:"+BaseUtil.logSQL(sql,args));
        
        List<AreaInfoVo> areaInfoVoList = jdbcTemplate.query(sql,args,new RowMapper<AreaInfoVo>() {
            public AreaInfoVo mapRow(ResultSet rs, int rowNum)throws SQLException {
                AreaInfoVo vo = new AreaInfoVo();
                AutoInjection.Rs2Vo(rs, vo, null);
                return vo;
            }
        });
        
        return areaInfoVoList;
    }

    @Override
    public List<StoreProductVo> getProvinceStoreProductVo(String productId, String areaId) {
        StringBuilder sb = new StringBuilder();
        sb.append("select sp.id,sp.pk_stordoc,sp.productid,sp.quantity,sp.allocatable,sp.supplyid ");
        sb.append(" from store_product sp ");
        sb.append(" left join relation_areastorage ra on ra.pk_stordoc = sp.pk_stordoc and ra.ISMAIN='4' ");//取天联在经的仓库
        sb.append(" left join bi_store bs on sp.pk_stordoc = bs.storecode and bs.storelevel='03' ");//取良品仓产品
        sb.append(" where sp.productId = ? and ra.areaId = ?");
        
        Object[] args = new Object[]{productId,areaId};
        args = StringHelperTools.nullToString(args);
  
        String sql = sb.toString();
        LOGGER.debug("查询store_product表:"+BaseUtil.logSQL(sql,args));
  
        List<StoreProductVo> list = jdbcTemplate.query(sql, args, new RowMapper<StoreProductVo>() {
            public StoreProductVo mapRow(ResultSet rs, int rowNum)throws SQLException {
                StoreProductVo vo = new StoreProductVo();
                AutoInjection.Rs2Vo(rs, vo, null);
                return vo;
            }
        });
  
        return list;
    }

    @Override
    public List<AreaInfoVo> getSupplyArea(String ...params) {
        String supply_id = params[0];
        String sql = "select a.id,a.supply_id,a.area_id areaid,a.parent_id parentid,b.areaname,b.areaLevel from supply_area a left join areainfo b on a.area_id=b.areaid where a.supply_id =? and a.del_flag=?";
        Object args [] = new Object[]{supply_id, TellingConstance.DEL_FLAG_1};
        
        LOGGER.debug("根据供应商ID查询销售区域信息: "+ BaseUtil.logSQL(sql,args));
        
        return jdbcTemplate.query(sql,args,new RowMapper<AreaInfoVo>() {
            public AreaInfoVo mapRow(ResultSet rs, int rowNum)throws SQLException {
                AreaInfoVo vo = new AreaInfoVo();
                AutoInjection.Rs2Vo(rs, vo, null);
                return vo;
            }
        });
    }
    @Override
    public List<BuyerVo> getBuyerinfoVo(String areaId,Map<String, String> buyerTypeMap) {
        List<BuyerVo> buyerVoList = new ArrayList<BuyerVo>();
        StringBuilder sb = new StringBuilder();
        String type="";
        if(buyerTypeMap!=null&&buyerTypeMap.size()>0){
        	type=getKey(buyerTypeMap);
        }
        if("".equals(areaId)){
        	//StringBuilder sb = new StringBuilder();帅选天联在线的用户（buyer_type IS NOT NULL）
        	 sb.append("WITH BI AS(SELECT  bi.buyerid,bi.buyer_type, u.username shopname,bi.areaid FROM BUYERINFO BI ") ;  
        	 sb.append("join users u on bi.userid=u.userid");              //讲用户名修改为username	 
             sb.append(" WHERE BI.BUYERSTATUS = '02' AND BI.DELFLAG = '1' AND bi.buyer_type IS NOT NULL AND bi.buyer_type IN ("+type+")");
             sb.append(" UNION ALL ");
             sb.append("SELECT CB.GBUYERID as buyerid,CB.BUYER_TYPE, u.username  as shopname, SHOPNAME,CB.DISTRICT as areaid");             
             sb.append(" FROM COMMON_BUYERINFO CB join users u on cb.userid=u.userid WHERE CB.STATUS = '1' AND CB.BUYER_TYPE IS NOT NULL  AND CB.BUYER_TYPE  IN ("+type+"))");
             sb.append(" select BI.* from BI");
        	
//            sb.append(" select bi.areaid,bi.buyerId,bi.shopname,bc.id as buyercategoryId,bc.buyercategoryName ");
//            sb.append(" from buyerInfo bi ");
//            sb.append(" left join relation_buyercategory rbc on rbc.buyerId = bi.buyerId and rbc.approveStatus = '02' ");
//            sb.append(" left join buyerCategory bc on bc.id = rbc.buyerCategoryId and bc.delFlag = '1' ");
//            sb.append(" where bi.delflag = '1' and bi.buyerstatus='02' ");
//              sb.append("select * from buyerinfo b where b.delflag = '1' and b.buyerstatus='02'");
            
            String sql = sb.toString();
            LOGGER.debug("获取所有的买家信息: "+ BaseUtil.logSQL(sql,new Object[]{}));
            buyerVoList = jdbcTemplate.query(sql,new RowMapper<BuyerVo>() {
                public BuyerVo mapRow(ResultSet rs, int rowNum) throws SQLException {
                    BuyerVo vo = new BuyerVo();
                    AutoInjection.Rs2Vo(rs, vo, null);
                    return vo;
                }
            });
        }else{
        	 sb.append("WITH BI AS(SELECT  bi.buyerid,bi.buyer_type,u.username shopname,bi.areaid  FROM BUYERINFO BI ");
        	 sb.append("join users u on bi.userid=u.userid");             //讲用户名修改为username	 
             sb.append(" WHERE BI.BUYERSTATUS = '02' AND BI.DELFLAG = '1' AND bi.buyer_type IS NOT NULL  AND bi.buyer_type IN ("+type+")");
             sb.append(" UNION ALL ");
             sb.append("SELECT CB.GBUYERID as buyerid,CB.BUYER_TYPE,u.username  as shopname,CB.DISTRICT as areaid");
             sb.append(" FROM COMMON_BUYERINFO CB join users u on cb.userid=u.userid WHERE CB.STATUS = '1' AND CB.BUYER_TYPE IS NOT NULL  AND CB.BUYER_TYPE  IN ("+type+"))");
             sb.append(" select BI.* from BI where bi.areaid in(");
             sb.append(" select  ai.areaid from areainfo ai  start with ai.areaid=?");
             sb.append("  connect by prior ai.areaid=ai.parentid)");
           // String sql = " select * from buyerinfo b where b.delflag = '1' and b.areaid = ? and b.buyerstatus='02' ";
            Object[] args = {areaId};
            args = StringHelperTools.nullToString(args);
            String sql=sb.toString();
            LOGGER.debug("根据areaId获取买家信息: "+ BaseUtil.logSQL(sql,args));
            buyerVoList = jdbcTemplate.query(sql, args, new RowMapper<BuyerVo>() {
                public BuyerVo mapRow(ResultSet rs, int rowNum) throws SQLException {
                    BuyerVo vo = new BuyerVo();
                    AutoInjection.Rs2Vo(rs, vo, null);
                    return vo;
                }
            });
        }
        return buyerVoList;
    }
    
    public String getKey(Map<String,String> buyerMap){
    	String str="";
    	for (Map.Entry<String, String> entry : buyerMap.entrySet()) {
    		if("".equals(str)){
    			str=entry.getKey();
    		}else{
    			str+=","+entry.getKey();
    		}
         }
    	return str;
    }

    @Override
    public List<SupplyArea0Vo> getCustomSupplyArea0(String shopProductId) {
        String sql = " select * from supply_area0 sa where sa.supplyproductid = ? and sa.customid is not null and sa.del_flag = ?";
        Object[] args = {shopProductId, TellingConstance.DEL_FLAG_1};
        args = StringHelperTools.nullToString(args);
        
        LOGGER.debug("根据shopProductId获取产品定向发布到客户的信息: "+ BaseUtil.logSQL(sql,args));
        List<SupplyArea0Vo> supplyArea0List = jdbcTemplate.query(sql,args,new RowMapper<SupplyArea0Vo>() {
            public SupplyArea0Vo mapRow(ResultSet rs, int rowNum) throws SQLException {
                SupplyArea0Vo vo = new SupplyArea0Vo();
                AutoInjection.Rs2Vo(rs, vo, null);
                return vo;
            }
        });
        
        return supplyArea0List;
    }

    @Override
    public AreaInfoVo getAreaInfoVo(String areaId) {
        String sql = "select * from areainfo where status='1' and areaid = ? ";
        Object[] args = {areaId};
        args = StringHelperTools.nullToString(args);
        
        LOGGER.debug("根据areaId获取区域信息: "+ BaseUtil.logSQL(sql,args));
        List<AreaInfoVo> areaInfoVoList = jdbcTemplate.query(sql,args,new RowMapper<AreaInfoVo>() {
            public AreaInfoVo mapRow(ResultSet rs, int rowNum) throws SQLException {
                AreaInfoVo vo = new AreaInfoVo();
                AutoInjection.Rs2Vo(rs, vo, null);
                return vo;
            }
        });
        
        if(areaInfoVoList != null && areaInfoVoList.size() > 0){
            return areaInfoVoList.get(0);
        }
        
        return null;
    }
    
    @Override
    public int insertDirectional(String shopProductId, String priceretailonline, String stockNumber, String areaId,
            String pk_stordoc, String userId, String gifts, String isContainInvoice) {
        int supplyArea0Id = 0;
        try{
        String sql = "update shop_product set isfeng = ? where id = ? ";
        Object args [] = new Object[]{ TellingConstance.FENG,shopProductId };
        args = StringHelperTools.nullToString(args);
        
        LOGGER.debug("更新产品指定供应商为定向发布模式:"+BaseUtil.logSQL(sql,args));
        
        //删除supply_area0非定向发布的数据，以防重复发布
        String sqldel = " update supply_area0 set del_flag = ?, update_user = ? where supplyproductid = ? and saletype = ? ";
        Object argsdel [] = new Object[]{TellingConstance.DEL_FLAG_0, userId, shopProductId, TellingConstance.SALETYPE2};
        argsdel = StringHelperTools.nullToString(argsdel);
        
        LOGGER.debug("删除supply_area0中重复供应商产品ID的非定向发布的数据:" + BaseUtil.logSQL(sqldel,argsdel));
        jdbcTemplate.update(sqldel, argsdel);
        
        //更新supply_area0表里面的数据
        String sqlSEQ =" select SEQ_SUPPLY_AREA0_PK.NEXTVAL from dual ";
        List<String> ls = (List<String>) jdbcTemplate.query(sqlSEQ,new RowMapper<String>() {
                    public String mapRow(ResultSet rs, int i)throws SQLException {
                        return rs.getString("NEXTVAL");
                    }
                });
        String supply_area0Id = "";
        if(null != ls && ls.size() > 0){
            supply_area0Id=ls.get(0);
        }
        
        // 插入supply_area0数据，默认审核通过，因为产品发布不需要审核即可使用
        //组合唯一键
		String onlyID=shopProductId+"_"+areaId+"_N";
        String sql2 = " insert into supply_area0(id,supplyproductid,areaId,priceretailonline,stocknumber,overplusNumber,pk_stordoc,salestatus,saletype,isvalid,del_flag,createTime,update_user,gifts,IS_CONTAIN_INVOICE,status,onshelftime,ONLYID) values (?,?,?,?,?,?,?,?,?,?,?,sysdate,?,?,?,?,sysdate,?) "; 
        Object args2 [] = new Object[]{supply_area0Id,shopProductId,areaId,
                            priceretailonline,stockNumber,stockNumber,pk_stordoc,
                            TellingConstance.SALESTATUS3,TellingConstance.SALETYPE4,
                            TellingConstance.IS_VALID_01,TellingConstance.DEL_FLAG_1,
                            userId,gifts,isContainInvoice,TellingConstance.STATUS_1,onlyID
                        };
        args2 = StringHelperTools.nullToString(args2);
        LOGGER.debug("插入一条定向到区域发布的数据:"+BaseUtil.logSQL(sql2,args2));
        
        jdbcTemplate.update(sql, args);
        jdbcTemplate.update(sql2, args2);
        supplyArea0Id = Integer.valueOf(supply_area0Id);
        
        }catch(Exception e){
        	e.printStackTrace();
        }
        return supplyArea0Id;
    }

    @Override
    public int insertDirectionalForCustom(String shopProductId, String priceretailonline, String stockNumber,
            String customId, String pk_stordoc, String userId, String gifts, String isContainInvoice) {
        int supplyArea0Id = 0;
        try{
        String sql = " update shop_product set isfeng = ? where id = ? ";
        Object args [] = new Object[]{ TellingConstance.FENG,shopProductId };
        args = StringHelperTools.nullToString(args);
        
        LOGGER.debug("更新产品指定供应商为定向发布模式:"+BaseUtil.logSQL(sql,args));
        
        //更新supply_area0表里面的数据
        String sqlSEQ =" select SEQ_SUPPLY_AREA0_PK.NEXTVAL from dual ";
        List<String> ls = (List<String>) jdbcTemplate.query(sqlSEQ,new RowMapper<String>() {
                    public String mapRow(ResultSet rs, int i)throws SQLException {
                        return rs.getString("NEXTVAL");
                    }
                });
        String supply_area0Id = "";
        if(null != ls && ls.size() > 0){
            supply_area0Id=ls.get(0);
        }
        //组合唯一键
        String onlyID=shopProductId+"_"+"N"+"_"+customId;
        String sql2 = " insert into supply_area0(id,supplyproductid,customId,priceretailonline,stocknumber,overplusNumber,pk_stordoc,salestatus,saletype,isvalid,del_flag,createTime,update_user,gifts,IS_CONTAIN_INVOICE,status,onshelftime,ONLYID) values (?,?,?,?,?,?,?,?,?,?,?,sysdate,?,?,?,?,sysdate,?) ";
        Object args2 [] = new Object[]{supply_area0Id,shopProductId,customId,
                            priceretailonline,stockNumber,stockNumber,pk_stordoc,
                            TellingConstance.SALESTATUS3,TellingConstance.SALETYPE3,
                            TellingConstance.IS_VALID_01,TellingConstance.DEL_FLAG_1,
                            userId,gifts,isContainInvoice,TellingConstance.STATUS_1,onlyID
                        };
        args2 = StringHelperTools.nullToString(args2);

        LOGGER.debug("插入一条定向到客户发布的数据:"+BaseUtil.logSQL(sql2,args2));
        
        jdbcTemplate.update(sql, args);
        jdbcTemplate.update(sql2, args2);
        
        supplyArea0Id = Integer.valueOf(supply_area0Id);
        }catch(Exception e){
        	e.printStackTrace();
        }
        return supplyArea0Id;
    }

    @Override
    public List<SupplyArea0Vo> checkIsCustomId(String shopProductId) {
        String sql = " select sa.id,sa.supplyProductId,sa.isValid,sa.areaId,sa.del_flag as delflag,sa.isContinuous,sa.priceRetailOnline,sa.stockNumber,sa.overplusNumber,sa.customId,sa.saleType,sa.isShare,sa.shareCode,sa.IS_CONTAIN_INVOICE as isContainInvoice from supply_area0 sa where sa.supplyproductid = ? and sa.customid is not null and sa.del_flag = ?";
        Object[] args = {shopProductId, TellingConstance.DEL_FLAG_1};
        args = StringHelperTools.nullToString(args);
        
        LOGGER.debug("根据customId查询指定客户定向记录列表：" + BaseUtil.logSQL(sql, args));
        List<SupplyArea0Vo> supplyArea0List = jdbcTemplate.query(sql,args,new RowMapper<SupplyArea0Vo>() {
            public SupplyArea0Vo mapRow(ResultSet rs, int rowNum) throws SQLException {
                SupplyArea0Vo vo = new SupplyArea0Vo();
                AutoInjection.Rs2Vo(rs, vo, null);
                return vo;
            }
        });
        
        return supplyArea0List;
    }

    @Override
    public List<SupplyArea0Vo> checkIsAreaId(String shopProductId) {
        String sql = " select sa.id,sa.supplyProductId,sa.isValid,sa.areaId,sa.del_flag as delflag,sa.isContinuous,sa.priceRetailOnline,sa.stockNumber,sa.overplusNumber,sa.customId,sa.saleType,sa.isShare,sa.shareCode,sa.IS_CONTAIN_INVOICE as isContainInvoice from supply_area0 sa where sa.supplyproductid = ? and sa.areaid is not null and sa.del_flag = ?";
        Object[] args = {shopProductId, TellingConstance.DEL_FLAG_1};
        args = StringHelperTools.nullToString(args);
        
        LOGGER.debug("根据areaId查询指定区域定向记录列表：" + BaseUtil.logSQL(sql, args));
        List<SupplyArea0Vo> supplyArea0List = jdbcTemplate.query(sql,args,new RowMapper<SupplyArea0Vo>() {
            public SupplyArea0Vo mapRow(ResultSet rs, int rowNum) throws SQLException {
                SupplyArea0Vo vo = new SupplyArea0Vo();
                AutoInjection.Rs2Vo(rs, vo, null);
                return vo;
            }
        });
        
        return supplyArea0List;
    }

    @Override
    public int insertDirectionalShare(String shopProId, String priceretailonline, String stockNumber, String areaId,
            String shareCode, String userId, String pk_stordoc, String gifts, String isContainInvoice) {
        int supplyArea0Id = 0;
        try{
        String sql = " update shop_product set isfeng = ? where id = ? ";
        Object args [] = new Object[]{ TellingConstance.FENG,shopProId };
        args = StringHelperTools.nullToString(args);
        
        LOGGER.debug("更新shop_product为定向发布模式:"+BaseUtil.logSQL(sql,args));
        
        //删除supply_area0数据
        String sqldel = " update supply_area0 set del_flag = ?, update_user = ? where supplyproductid = ? and saletype = ? ";
        Object argsdel [] = new Object[]{TellingConstance.DEL_FLAG_0, userId, shopProId, TellingConstance.SALETYPE2};
        argsdel = StringHelperTools.nullToString(argsdel);
                
        LOGGER.debug("删除supply_area0非定向数据:"+BaseUtil.logSQL(sqldel,argsdel));
        jdbcTemplate.update(sqldel, argsdel);
                
        //更新supply_area0表里面的数据
        String sqlSEQ =" select SEQ_SUPPLY_AREA0_PK.NEXTVAL from dual ";
        List<String> ls = (List<String>) jdbcTemplate.query(sqlSEQ,new RowMapper<String>() {
                    public String mapRow(ResultSet rs, int i)throws SQLException {
                        return rs.getString("NEXTVAL");
                    }
                });
        String supply_area0Id = "";
        if(null != ls && ls.size()>0){
            supply_area0Id=ls.get(0);
        }
      //组合唯一键
        String onlyID=shopProId+"_"+areaId+"_N";
        String sql2 = " insert into supply_area0(id,supplyproductid,areaId,priceretailonline,stocknumber,overplusNumber,pk_stordoc,salestatus,saletype,isvalid,del_flag,createTime,isShare,shareCode,shareTime,update_user,gifts,IS_CONTAIN_INVOICE,status,onshelftime,ONLYID) values (?,?,?,?,?,?,?,?,?,?,?,sysdate,'1',?,sysdate,?,?,?,?,sysdate,?) "; 
        Object args2 [] = new Object[]{supply_area0Id,shopProId,areaId,
                            priceretailonline,stockNumber,stockNumber,pk_stordoc,
                            TellingConstance.SALESTATUS3,TellingConstance.SALETYPE4,
                            TellingConstance.IS_VALID_01,TellingConstance.DEL_FLAG_1,
                            shareCode,userId,gifts,isContainInvoice,TellingConstance.STATUS_1,onlyID
                        };
        args2 = StringHelperTools.nullToString(args2);
        
        LOGGER.debug("插入一条定向发布区域共享库存的数据:"+BaseUtil.logSQL(sql2,args2));
        
        jdbcTemplate.update(sql, args);
        jdbcTemplate.update(sql2,args2);
        supplyArea0Id = Integer.valueOf(supply_area0Id);
    }catch(Exception e){
    	e.printStackTrace();
    }
        return supplyArea0Id;
    }

    @Override
    public int insertDirectionalShareForCustom(String shopProId, String priceretailonline, String stockNumber,
            String customId, String shareCode, String userId, String pk_stordoc, String gifts, String isContainInvoice) {
        int supplyArea0Id = 0;
      try{
        String sql = " update shop_product set isfeng = ? where id = ? ";
        Object args [] = new Object[]{ TellingConstance.FENG,shopProId };
        args = StringHelperTools.nullToString(args);
        
        LOGGER.debug("更新shop_product为定向发布模式:"+BaseUtil.logSQL(sql,args));
        
        //更新supply_area0表里面的数据
        String sqlSEQ =" select SEQ_SUPPLY_AREA0_PK.NEXTVAL from dual ";
        List<String> ls = (List<String>) jdbcTemplate.query(sqlSEQ,new RowMapper<String>() {
                    public String mapRow(ResultSet rs, int i)throws SQLException {
                        return rs.getString("NEXTVAL");
                    }
                });
        String supply_area0Id = "";
        if(null != ls && ls.size() > 0){
            supply_area0Id=ls.get(0);
        }
        //组合唯一键
        String onlyID=shopProId+"_"+"N"+"_"+customId;
        String sql2 = " insert into supply_area0(id,supplyproductid,customId,priceretailonline,stocknumber,overplusNumber,pk_stordoc,salestatus,saletype,isvalid,del_flag,createTime,isShare,shareCode,shareTime,update_user,gifts,IS_CONTAIN_INVOICE,status,onshelftime,ONLYID) values (?,?,?,?,?,?,?,?,?,?,?,sysdate,'1',?,sysdate,?,?,?,?,sysdate,?) ";
        Object args2 [] = new Object[]{supply_area0Id, shopProId,customId,
                            priceretailonline,stockNumber,stockNumber,pk_stordoc,
                            TellingConstance.SALESTATUS3,TellingConstance.SALETYPE3,
                            TellingConstance.IS_VALID_01,TellingConstance.DEL_FLAG_1,
                            shareCode,userId,gifts,isContainInvoice,TellingConstance.STATUS_1,onlyID
                        };
        args2 = StringHelperTools.nullToString(args2);

        LOGGER.debug("更新supply_area0表的数据:"+BaseUtil.logSQL(sql2,args2));
        
        jdbcTemplate.update(sql, args);
        jdbcTemplate.update(sql2,args2);
        supplyArea0Id = Integer.valueOf(supply_area0Id);
	    }catch(Exception e){
	    	e.printStackTrace();
	    }
        return supplyArea0Id;
    }

    @Override
    public List<SupplyArea0Vo> getSupplyArea0ByIds(String ids) {
        String sql = " select sa.id,sa.supplyProductId,sa.isValid,sa.areaId,sa.del_flag as delflag,sa.isContinuous,sa.priceRetailOnline,sa.stockNumber,sa.overplusNumber,sa.customId,sa.saleType,sa.isShare,sa.shareCode,sa.IS_CONTAIN_INVOICE as isContainInvoice,sp.productId from supply_area0 sa INNER JOIN SHOP_PRODUCT sp on sa.SUPPLYPRODUCTID=sp.ID where sa.id in ("+ids+")";
        
        LOGGER.debug("根据主键ID获取产品发布的信息: "+ sql);
        List<SupplyArea0Vo> supplyArea0List = jdbcTemplate.query(sql,new RowMapper<SupplyArea0Vo>() {
            public SupplyArea0Vo mapRow(ResultSet rs, int rowNum) throws SQLException {
                SupplyArea0Vo vo = new SupplyArea0Vo();
                AutoInjection.Rs2Vo(rs, vo, null);
                return vo;
            }
        });
        
        return supplyArea0List;
    }

    @Override
    public List<SupplyArea0Vo> getSupplyArea0ByShopProductId(String shopProductId) {
        String sql = " select sa.id,sa.supplyProductId,sa.isValid,sa.areaId,sa.del_flag as delflag,sa.isContinuous,sa.priceRetailOnline,sa.stockNumber,sa.overplusNumber,sa.customId,sa.saleType,sa.isShare,sa.shareCode,sa.IS_CONTAIN_INVOICE as isContainInvoice,sp.productId from supply_area0 sa INNER JOIN SHOP_PRODUCT sp on sa.SUPPLYPRODUCTID=sp.ID where sa.DEL_FLAG='1' and sa.SUPPLYPRODUCTID=? and sa.SALETYPE=?";
        Object[] args = new Object[]{shopProductId, TellingConstance.SALETYPE2};
        args = StringHelperTools.nullToString(args);
        
        LOGGER.debug("根据产品指定供应ID获取产品发布的信息: "+ BaseUtil.logSQL(sql, args));
        List<SupplyArea0Vo> supplyArea0List = jdbcTemplate.query(sql, args,new RowMapper<SupplyArea0Vo>() {
            public SupplyArea0Vo mapRow(ResultSet rs, int rowNum) throws SQLException {
                SupplyArea0Vo vo = new SupplyArea0Vo();
                AutoInjection.Rs2Vo(rs, vo, null);
                return vo;
            }
        });
        
        return supplyArea0List;
    }

	@Override
	public List<SupplyArea0Vo> getSupplyArea0ByShareCode(String shareCode) {
		String sql = " select sa.id,sa.supplyProductId,sa.isValid,sa.areaId,sa.del_flag as delflag,sa.isContinuous,sa.priceRetailOnline,sa.stockNumber,sa.overplusNumber,sa.customId,sa.saleType,sa.isShare,sa.shareCode,sa.IS_CONTAIN_INVOICE as isContainInvoice,sp.productId from supply_area0 sa INNER JOIN SHOP_PRODUCT sp on sa.SUPPLYPRODUCTID=sp.ID where sa.DEL_FLAG='1' and sa.shareCode=?";
		Object[] args = new Object[]{shareCode};
		args = StringHelperTools.nullToString(args);
		LOGGER.debug("根据共享编码查询产品发布信息: "+ BaseUtil.logSQL(sql, args));
	    List<SupplyArea0Vo> supplyArea0List = jdbcTemplate.query(sql, args,new RowMapper<SupplyArea0Vo>() {
	            public SupplyArea0Vo mapRow(ResultSet rs, int rowNum) throws SQLException {
	                SupplyArea0Vo vo = new SupplyArea0Vo();
	                AutoInjection.Rs2Vo(rs, vo, null);
	                return vo;
	            }
	        });
	        
	        return supplyArea0List;
	}

	// 判断产品是否已指定非天联华建供应商
	@Override
	public int doCheck(String supplyId, String ProductId) {
		String str = "select count(*) from shop_product sp where sp.supplyid=? and sp.productid=? and sp.isvalid='01' and sp.del_flag='1'";
		String sql = "select sp.id from shop_product sp where sp.supplyid=? and sp.productid=? and sp.isvalid='01' and sp.del_flag='1'";
		Object[] args=new Object[]{supplyId,ProductId};
		LOGGER.debug("检查产品是否已指定此供应商:" + BaseUtil.logSQL(str, args));
		LOGGER.debug("检查产品是否已指定此供应商:" + BaseUtil.logSQL(sql, args));
		if(jdbcTemplate.queryForInt(str, args)==0){
			return 0;
		}
		return jdbcTemplate.queryForInt(sql, args);
	}

	// 获取天联在线产品
	@Override
	public List<Map<String, String>> loadProductList(String userId, String sortId1, String sortId2,String productName,String saleType,String status,String leafBrandId,String isLeafBrand) {
		StringBuilder str = new StringBuilder();
		//定向发布
		String sql = "select pm.productId " + 
				 " from shop_product sp " +
				 " inner join product_main pm on pm.productId = sp.productId and pm.del_flag = '1' " +
				 " inner join supply s on s.id =  sp.supplyId and s.del_flag = '1' " +
				 " inner join supply_area0 sa on sa.supplyProductId = sp.id and sa.del_flag = '1' and sa.isValId = '01' " +
				 " where s.user_id = '" + userId + "' " +
				 " and (sa.saleStatus = '1' or (sa.status in ('1','2') and sa.saleStatus <> '1')) "  +
				 " and sa.saleType in ('" + saleType + "') "+
				 " and sp.del_flag = '1' and sp.isValId = '01' " +
				 " group by sp.id, pm.productId";
		//非定向发布
		String sql0="select distinct pm.productid from " +
				" supply_area0 sa inner join shop_product sp on sp.id = sa.supplyproductid and sp.isvalid = '01'and sp.del_flag = '1'" +
				" inner join product_main pm on pm.productid = sp.productid and pm.del_flag = '1' "+
				" inner join supply s on s.id = sp.supplyid and s.del_flag = '1' "+
				" where s.user_id = "+userId +"  and (sa.saleStatus = '1' or (sa.status in ('1', '2') and sa.saleStatus <> '1'))"+
				" and sa.saleType in ('2', '3', '4') and sa.isvalid = '01' and sa.del_flag = '1'";
		str.append("select * from (select rownum rn, p.* from( ");
		str.append("select pm.productId,pm.productname  from product_main pm,product_brand pb   ");
		str.append(" where pm.brandid = pb.brandId  and pm.del_flag='1'   and pm.publish='1' and pm.saleType='2'");
		str.append(" and pm.productid not in( ");
		if(saleType==null){
			str.append(sql0+")");
		}else{
			str.append(sql+")");
		}
		List<Object> args = new ArrayList<Object>();
		if(!"".equals(sortId2)){
			if("yes".equals(sortId1)){
				str.append(" and pm.SORTID = ?");
	            args.add(sortId2);
			}else if("no".equals(sortId1)){ 
				str.append(" and pm.SORTID in (select SORTID from product_sort where DEL_FLAG=? and ISVALID=? start with PARENTID=? connect by prior  SORTID=PARENTID)");
	            args.add(TellingConstance.DEL_FLAG_1);
	            args.add(TellingConstance.IS_VALID_01);
	            args.add(sortId2);
			}
		}
		if(!"".equals(leafBrandId)){
			if("yes".equals(isLeafBrand)){
				str.append(" and pb.brandid = ?");
	            args.add(leafBrandId);
			}else if("no".equals(isLeafBrand)){ 
				str.append("and pb.brandid in (select pb.brandid from product_brand pb where pb.del_flag=? and pb.isvalid=? start with pb.parentid=?  connect by prior pb.brandid=pb.parentid)");
	            args.add(TellingConstance.DEL_FLAG_1);
	            args.add(TellingConstance.IS_VALID_01);
	            args.add(leafBrandId);
			}
		}
		if(!"".equals(productName)) {
            str.append(" and lower(replace(pm.productname,' ','') ) like ?");
            args.add("%"+productName.replaceAll("\\s*", "").toLowerCase()+"%");
        }
        str.append(" order by pm.productname");
        str.append(" ) p ) where rn>0 and rn<=100");
		LOGGER.debug("获取产品列表:" + BaseUtil.logSQL(str.toString(), args.toArray()));
		List<Map<String, String>> list = jdbcTemplate.query(str.toString(),args.toArray(),
				new ValueMapperSelf());
		return list;
	}

	// 整合查询数据
	public static final class ValueMapperSelf implements
			RowMapper<Map<String, String>> {
		public Map<String, String> mapRow(ResultSet rs, int rowNum)
				throws SQLException {
			Map<String, String> map = new HashMap<String, String>();
			map.put("productId",StringHelperTools.nvl(rs.getString("productId")));
			map.put("productName", StringHelperTools.nvl(rs.getString("productName")));
            map.put("checked", "0");// 默认未选中
			return map;
		}
	}

	// 向shop_product插入一条记录
	@Override
	public int doInsert(String supplyId,String productId) {
		String sql = " SELECT SEQ_SHOP_PRODUCT_KEY.NEXTVAL FROM dual ";
		String shopProId = "";
		List<String> la = (List<String>) jdbcTemplate.query(sql,
				new RowMapper<String>() {
					public String mapRow(ResultSet rs, int i)
							throws SQLException {
						return rs.getString("NEXTVAL");
					}
				});
		if (la.size() > 0 && la != null) {
			shopProId = la.get(0);
		}
		String shopIdSql = "select si.id from shop_info si "
				+ " inner join supply s on si.selleruserid = s.user_id and s.id = ? where si.del_flag = '1' ";
		String shopId = "";
		LOGGER.debug("添加指定供应商模式产品:" + BaseUtil.logSQL(shopIdSql,new Object[] { supplyId }));
		List<String> strLs = jdbcTemplate.query(shopIdSql,
				new Object[] { supplyId }, new RowMapper<String>() {
					public String mapRow(ResultSet rs, int rowNum)
							throws SQLException {
						return rs.getString("id");
					}
				});
		if (strLs != null && strLs.size() > 0) {
			shopId = strLs.get(0);
			System.out.println("获取供应商商店ID:"+shopId);
		}
		//唯一键约束
		String onlyspid=productId+"_"+supplyId;
		String sql2 = " INSERT INTO shop_product(id,productId,isvalid,del_flag,supplyId,salemode,shopId,approvetime,source,onlyspid) VALUES(?,?,?,?,?,?,?,sysdate,'1',?)";
		Object[] args = new Object[] { 
				shopProId, 
				productId,
				TellingConstance.IS_VALID_01, 
				TellingConstance.DEL_FLAG_1,
				supplyId, 
				TellingConstance.APPOINTMODE, 
				shopId,
				onlyspid};
		args = StringHelperTools.nullToString(args);
		LOGGER.debug("添加指定供应商模式产品:" + BaseUtil.logSQL(sql2, args));
		int resultNum=0;
		try{
			resultNum = jdbcTemplate.update(sql2, args);
		}catch(Exception e){
			LOGGER.debug("同一个产品对于同 一个供应商只能有一条有效数据：" +e.getMessage());
			e.printStackTrace();
		}
		return resultNum;
	}

	@Override
	public List<Map<String, String>> queryNewProductList(String userId,String saleType) {
		StringBuffer sb = new StringBuffer();
		sb.append("select sp.productid,sp.id as shopProductId from shop_product sp");
		sb.append(" inner join supply s on s.id=sp.supplyid");
		sb.append(" inner join product_main pm on pm.productid=sp.productid ");
		sb.append(" and pm.del_flag='"+TellingConstance.DEL_FLAG_1+"'");
		sb.append(" where sp.del_flag='"+TellingConstance.DEL_FLAG_1+"' and s.del_flag='"+TellingConstance.DEL_FLAG_1+"'");
		sb.append(" and s.user_id='"+userId+"'");
		sb.append(" and not exists (");
		sb.append(" select sa.id from supply_area0 sa");
		sb.append(" where sa.supplyproductid=sp.id and sa.del_flag='"+TellingConstance.DEL_FLAG_1+"'");
		if(!"".equals(saleType)){
		sb.append(" and sa.saleType = '"+saleType+"'");	
		}else{
		sb.append(" and (sa.saleType ='2' or sa.saleType='3' or sa.saleType='4') ");	
		}
		sb.append(" )");
		Object[] args=new Object[]{};
		LOGGER.debug("已指定未发布的产品数据:" + BaseUtil.logSQL(sb.toString(), args));
		List<Map<String, String>> list = jdbcTemplate.query(sb.toString(), args, new ValueMapperSelf3());
		return list;
	}
	
	private static final class ValueMapperSelf3 implements RowMapper<Map<String, String>> {
        public Map<String, String> mapRow(ResultSet rs, int rowNum) throws SQLException {
            Map<String, String> map = new HashMap<String, String>();
            map.put("productId",StringHelperTools.nvl(rs.getString("productId")));
            map.put("shopProductId",StringHelperTools.nvl(rs.getString("shopProductId")));
            return map;
        }
    }
}